﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Configuration;
using System.Security.Cryptography;
using System.IO;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DatabaseUpdater
{
    class Program
    {
        static String _updateScriptsFolder;
        static DataSet _dsInstalledScripts;
        static Boolean _setupFlag = false;
        static Boolean _noExecuteFlag = false;
        static Boolean _updateFlag = false;

        static DataSet dsInstalledScripts
        {
            get
            {
                if (_dsInstalledScripts == null)
                {
                    GetInstalledScriptsList();
                }
                return _dsInstalledScripts;
            }
        }

        /// <summary>
        /// Returns codes: 0 - success, 1 - configuration error(s), 2 - error(s) in update scripts
        /// </summary>
        /// <param name="args">Arguments</param>
        /// <returns>Code</returns>
        static Int32 Main(String[] args)
        {
            try
            {
                ReadConfigurations(args);
            }
            catch (ArgumentException ex)
            {
                Console.WriteLine(ex.Message);
                return 1;
            }

            if (_setupFlag)
            {
                Console.Write("Adding database schema...");
                ExecuteTSQLScript(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "setup.sql"));
                Console.WriteLine("Done");
            }

            if (_updateFlag)
            {
                Console.WriteLine("Searching for update scripts...");
                DirectoryInfo scriptsFolderInfo = new DirectoryInfo(_updateScriptsFolder);
                Int32 counter = 0;
                Int32 counterFailed = 0;
                foreach (FileInfo scriptFile in scriptsFolderInfo.GetFiles("*.sql"))
                {
                    byte[] hash = CalculateFileHash(scriptFile.FullName);
                    if (!IsUpdateScriptInstalled(scriptFile.Name, hash))
                    {
                        Console.WriteLine(String.Empty);
                        String errorMessage = String.Empty;
                        if (!_noExecuteFlag)
                        {
                            try
                            {
                                Console.Write("Installing script '{0}'... ", scriptFile.Name);
                                ExecuteTSQLScript(scriptFile.FullName);
                                Console.WriteLine("Installed");
                            }
                            catch (Exception ex)
                            {
                                errorMessage = ex.Message;
                                Console.WriteLine("Failed: {0}", errorMessage);
                                counterFailed++;
                            }
                        }
                        else
                        {
                            Console.WriteLine("Adding script '{0}'... Done", scriptFile.Name);
                        }
                        SaveUpdateScriptInfo(scriptFile.Name, hash, errorMessage);
                        counter++;
                    }
                }
                if (counter == 0)
                {
                    Console.WriteLine("No scripts found to install.");
                }
                else
                {
                    Console.WriteLine(String.Empty);
                    Int32 totalInstalled = counter - counterFailed;
                    if (totalInstalled > 0)
                    {
                        Console.WriteLine("{0} script{1} {2}.", totalInstalled, totalInstalled > 1 ? "s" : "", _noExecuteFlag ? "added" : "installed");
                    }
                    if (counterFailed > 0)
                    {
                        Console.WriteLine("{0} script{1} failed.", counterFailed, counterFailed > 1 ? "s" : "");
                    }
                }

                if (counterFailed > 0)
                {
                    return 2;
                }
            }

            return 0;
        }

        /// <summary>
        /// Reads configuration settings.
        /// </summary>
        /// <param name="args">Arguments</param>
        static void ReadConfigurations(String[] args)
        {
            if (args.Length > 0)
            {   
                for (Int32 i = 0; i < args.Length; i++)
                {
                    if (args[i].Trim().ToLower().Equals("-setup"))
                    {
                        _setupFlag = true;
                    }

                    if (args[i].Trim().ToLower().Equals("-no_execute"))
                    {
                        _noExecuteFlag = true;
                    }

                    if (args[i].Trim().ToLower().Equals("-update"))
                    {
                        _updateFlag = true;
                    }
                }
            }

            if (ConfigurationManager.AppSettings["ScriptsFolder"] == null 
                || String.IsNullOrEmpty(ConfigurationManager.AppSettings["ScriptsFolder"].ToString()))
            {
                throw new ConfigurationErrorsException("Scripts Folder has not specified.");
            }
            else
            {
                _updateScriptsFolder = ConfigurationManager.AppSettings["ScriptsFolder"].ToString();
            }

            // Check folder for existence
            if (!Directory.Exists(_updateScriptsFolder))
            {
                throw new ArgumentException(String.Format("'{0}' doesn't exist.", _updateScriptsFolder));
            }

            if (!_updateFlag && !_setupFlag)
            {
                ShowHelp();
            }
        }

        static void ShowHelp()
        {
            Console.WriteLine("Usage: DatabaseUpdater [-update | -setup [-no_execute]]");
            Console.WriteLine(" -update: update the database");
            Console.WriteLine(" -setup: install database schema necessary for saving information about installed scripts");
            Console.WriteLine(" -no_execute: do not execute files only update information about scripts. You can use it to add already installed scripts");
        }

        /// <summary>
        /// Determinates whether script is already installed.
        /// </summary>
        /// <param name="scriptName">Script name</param>
        /// <param name="hash">Hash of script</param>
        /// <returns>True if installed and false if not</returns>
        static Boolean IsUpdateScriptInstalled(String scriptName, byte[] hash)
        {
            Boolean result = false;
            String strHash = Convert.ToBase64String(hash);
            foreach (DataRow row in dsInstalledScripts.Tables[0].Rows)
            {
                if (Convert.ToString(row["ScriptName"]).Equals(scriptName) && strHash.Equals(Convert.ToBase64String((byte[])row["Hash"])))
                {
                    result = true;
                }
            }
            return result;
        }

        /// <summary>
        /// Executes T-SQL script.
        /// </summary>
        /// <param name="filePath">Full path to a script</param>
        static void ExecuteTSQLScript(String filePath)
        {
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    using (StreamReader sr = new StreamReader(filePath))
                    {
                        while (!sr.EndOfStream)
                        {
                            StringBuilder sql = new StringBuilder();
                            while (!sr.EndOfStream)
                            {
                                string s = sr.ReadLine();
                                if (s != null && s.ToUpper().Trim().Equals("GO"))
                                {
                                    break;
                                }
                                sql.AppendLine(s);
                            }
                            // Check for empty
                            if (sql.ToString().Trim() != String.Empty)
                            {
                                db.ExecuteNonQuery(transaction, CommandType.Text, sql.ToString());
                            }
                        }
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Saves information about update script in the database.
        /// </summary>
        /// <param name="fileName">Filename</param>
        /// <param name="hash">Hash of script</param>
        /// <param name="errorMessage">Error message</param>
        static void SaveUpdateScriptInfo(String fileName, byte[] hash, String errorMessage)
        {
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand command = db.GetStoredProcCommand("usp_DatabaseUpdate_Add"))
            {
                db.AddInParameter(command, "@ScriptName", DbType.String, fileName);
                db.AddInParameter(command, "@Hash", DbType.Binary, hash);
                db.AddInParameter(command, "@UpdateDate", DbType.DateTime, DateTime.Now);
                if (!String.IsNullOrEmpty(errorMessage))
                {
                    db.AddInParameter(command, "@ErrorMessage", DbType.String, errorMessage);
                }
                db.ExecuteNonQuery(command);
            }
        }

        /// <summary>
        /// Returns the list of installed scripts from the database.
        /// </summary>
        static void GetInstalledScriptsList()
        {
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand command = db.GetStoredProcCommand("usp_DatabaseUpdates_SelectAll"))
            {
                _dsInstalledScripts = db.ExecuteDataSet(command);
            }
        }

        /// <summary>
        /// Calculates file's hash.
        /// </summary>
        /// <param name="filePath">Path to file</param>
        /// <returns>Hash value</returns>
        static byte[] CalculateFileHash(String filePath)
        {
            SHA512 hashService = new SHA512Managed();
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            BinaryReader reader = new BinaryReader(fs);
            hashService.ComputeHash(reader.ReadBytes((Int32)fs.Length));
            return hashService.Hash;
        }
    }
}